var SPREADSHEET_ID_GABUNGAN = '15StReBuug-Azn9R7kMBh7CI9DFsH5R72Ji7ZwK-UPSU'; // Ganti dengan ID spreadsheet gabungan

// ===== FUNGSI doGet =====
function doGet(e) {
  try {
    const mode = e.parameter.mode;
    
    if (mode === 'test') {
      return ContentService
        .createTextOutput(JSON.stringify({ 
          status: 'ok', 
          message: 'API Gabungan is working!', 
          time: new Date().toLocaleString('id-ID', { timeZone: 'Asia/Jakarta' })
        }))
        .setMimeType(ContentService.MimeType.JSON);
    }
    
    return ContentService
      .createTextOutput(JSON.stringify({ status: 'error', message: 'Use mode=test for GET request' }))
      .setMimeType(ContentService.MimeType.JSON);
      
  } catch (error) {
    return ContentService
      .createTextOutput(JSON.stringify({ status: 'error', message: error.toString() }))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

// ===== FUNGSI doPost =====
function doPost(e) {
  try {
    const mode = e.parameter.mode;
    const sheetName = e.parameter.sheet;
    const data = JSON.parse(e.parameter.data);
    
    Logger.log('Mode: ' + mode);
    Logger.log('Sheet: ' + sheetName);
    
    if (mode === 'kirim') {
      const result = updateGabunganSheet(sheetName, data);
      
      return ContentService
        .createTextOutput(JSON.stringify({ status: 'success', message: result }))
        .setMimeType(ContentService.MimeType.JSON);
    }
    
    return ContentService
      .createTextOutput(JSON.stringify({ status: 'error', message: 'Invalid mode' }))
      .setMimeType(ContentService.MimeType.JSON);
      
  } catch (error) {
    Logger.log('Error: ' + error.toString());
    return ContentService
      .createTextOutput(JSON.stringify({ status: 'error', message: error.toString() }))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

// ===== FUNGSI: Update Sheet Gabungan =====
function updateGabunganSheet(sheetName, data) {
  const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID_GABUNGAN);
  let sheet = spreadsheet.getSheetByName(sheetName);
  
  Logger.log('updateGabunganSheet - Sheet: ' + sheetName);
  Logger.log('Siswa count: ' + (data.siswa ? data.siswa.length : 0));
  
  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
    Logger.log('Sheet created: ' + sheetName);
  } else {
    sheet.clear();
    Logger.log('Sheet cleared: ' + sheetName);
  }
  
  const siswa = data.siswa || [];
  const namaKelas = data.nama_kelas || '';
  const tanggal = data.tanggal || new Date().toLocaleString('id-ID', { timeZone: 'Asia/Jakarta' });
  
  // Header - gabungan semua kolom (termasuk Absensi)
  const headers = [
    'No', 'NIS/NISN', 'Nama Lengkap',
    // Ekskul (6 kolom)
    'Kepramukaan', 'Muhadloroh', 'Qiroah', 'Kaligrafi', 'Drumband', 'Paskibra',
    // Kepribadian (5 kolom)
    'Kedisiplinan', 'Tanggung Jawab', 'Kerapihan', 'Spiritual', 'Sosial',
    // Absensi (3 kolom) - BARU
    'Izin', 'Sakit', 'Ghoib',
    // Catatan
    'Catatan',
    // Kenaikan & Akademik
    'Total Nilai', 'Rata-rata', 'Ranking', 'Keterangan'
  ];
  
  // Tulis header
  if (headers.length > 0) {
    const headerRange = sheet.getRange(1, 1, 1, headers.length);
    headerRange.setValues([headers]);
    headerRange.setFontWeight('bold');
    headerRange.setBackground('#1a5276');
    headerRange.setFontColor('#ffffff');
  }
  
  // Baris info kelas dan tanggal
  sheet.getRange(2, 1).setValue('Kelas: ' + namaKelas);
  sheet.getRange(2, 4).setValue('Update: ' + tanggal);
  sheet.getRange(2, 4).setFontStyle('italic').setFontColor('#666666');
  
  // Data siswa
  const dataRows = [];
  siswa.forEach((s) => {
    const row = [];
    row.push(s.no || '');
    row.push(s.nis || '');
    row.push(s.nama || '');
    
    // Ekskul (index 3-8)
    row.push(s.kepramukaan || '-');
    row.push(s.muhadloroh || '-');
    row.push(s.qiroah || '-');
    row.push(s.kaligrafi || '-');
    row.push(s.drumband || '-');
    row.push(s.paskibra || '-');
    
    // Kepribadian (index 9-13)
    row.push(s.kedisiplinan || '-');
    row.push(s.tanggung_jawab || '-');
    row.push(s.kerapihan || '-');
    row.push(s.spiritual || '-');
    row.push(s.sosial || '-');
    
    // Absensi (index 14-16) - BARU
    row.push(s.izin || 0);
    row.push(s.sakit || 0);
    row.push(s.ghoib || 0);
    
    // Catatan (index 17)
    row.push(s.catatan || '-');
    
    // Kenaikan & Akademik (index 18-21)
    row.push(s.total || 0);
    row.push(s.rata || 0);
    row.push('#' + (s.rank || '-'));
    row.push(s.keterangan || '-');
    
    dataRows.push(row);
  });
  
  if (dataRows.length > 0) {
    sheet.getRange(3, 1, dataRows.length, headers.length).setValues(dataRows);
  }
  
  // Format
  if (headers.length > 0) {
    sheet.autoResizeColumns(1, headers.length);
  }
  sheet.setFrozenRows(2);
  
  // Border
  const lastRow = dataRows.length + 2;
  if (lastRow >= 3 && headers.length > 0) {
    sheet.getRange(1, 1, lastRow, headers.length).setBorder(true, true, true, true, true, true);
  }
  
  // Warna untuk Keterangan (kolom terakhir - index 21, kolom ke-22)
  if (dataRows.length > 0) {
    for (let i = 0; i < dataRows.length; i++) {
      const keterangan = dataRows[i][21]; // Kolom Keterangan (index 21)
      const rowNum = i + 3;
      if (keterangan === 'Naik') {
        sheet.getRange(rowNum, 22).setBackground('#d5f5e3').setFontColor('#1e7e34').setFontWeight('bold');
      } else if (keterangan === 'Tidak Naik') {
        sheet.getRange(rowNum, 22).setBackground('#f8d7da').setFontColor('#721c24').setFontStyle('italic');
      }
    }
  }
  
  // Warna untuk Ranking (kolom ke-21, index 20)
  if (dataRows.length > 0) {
    for (let i = 0; i < dataRows.length; i++) {
      const rankValue = parseInt(dataRows[i][20].replace('#', '')) || 0; // Kolom Ranking (index 20)
      const rowNum = i + 3;
      if (rankValue === 1) {
        sheet.getRange(rowNum, 21).setBackground('#ffd700').setFontColor('#000');
      } else if (rankValue === 2) {
        sheet.getRange(rowNum, 21).setBackground('#c0c0c0').setFontColor('#000');
      } else if (rankValue === 3) {
        sheet.getRange(rowNum, 21).setBackground('#cd7f32').setFontColor('#fff');
      }
    }
  }
  
  // Warna untuk Absensi - jika ada nilai > 0
  if (dataRows.length > 0) {
    for (let i = 0; i < dataRows.length; i++) {
      const izin = parseInt(dataRows[i][14]) || 0;
      const sakit = parseInt(dataRows[i][15]) || 0;
      const ghoib = parseInt(dataRows[i][16]) || 0;
      const rowNum = i + 3;
      
      // Izin - kuning muda
      if (izin > 0) {
        sheet.getRange(rowNum, 15).setBackground('#fff8e1');
      }
      // Sakit - hijau muda
      if (sakit > 0) {
        sheet.getRange(rowNum, 16).setBackground('#e8f5e9');
      }
      // Ghoib - merah muda
      if (ghoib > 0) {
        sheet.getRange(rowNum, 17).setBackground('#ffebee');
      }
    }
  }
  
  Logger.log('Gabungan sheet updated: ' + dataRows.length + ' siswa');
  
  return 'Sheet Gabungan untuk ' + namaKelas + ' berhasil diupdate (' + dataRows.length + ' siswa)';
}